from nonebot.log import logger
import sqlite3
from requests import post, get
import datetime
import re
import json
import datetime
from nonebot.log import logger
import sqlite3
from requests import post, get
import datetime
import re


def get_now() -> str:
    """返回当前时间"""
    return datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')


def one_talk():
    data = post(
        url='https://v1.hitokoto.cn/?c=d&c=e&c=h&c=i&c=k',
        json={},
        data={},
        headers={
            "Content-Type":
            "application/json;charset=UTF-8",
            "Cookie":
            "_ga=GA1.1.547256334.1692326280; _ga_74ZZC03H3M=GS1.1.1692326279.1.1.1692326300.0.0.0; _ga_QL2J611R9Q=GS1.1.1692354643.3.0.1692354643.0.0.0"
        }).json()


def get_now() -> str:
    """返回当前时间"""
    return datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')


def read_2_list(file_path):
    with open(file_path, 'r') as file:
        lines = file.readlines()
    black_list = set()
    for line in lines:
        black_list.add(line.strip())
    logger.debug(f'已载入黑名单列表: {black_list}')
    return list(black_list)


def add_black(black_num,group_id:str):
    with open(f'./{group_id}_black_list.txt', 'a', encoding='utf-8') as file:
        file.write(f'{black_num}\n')
    logger.info(f'添加黑名单{black_num} ok!')


class DB_Utils():

    def __init__(self, db_file):
        try:
            self.conn = sqlite3.connect(db_file)
            self.cursor = self.conn.cursor()
            logger.info(f'数据库初始化/链接成功!', extra={"markup": True})
        except Exception as e:
            logger.error(f'[bold red blink]数据库初始化/连接失败: {e} [/]',
                         extra={"markup": True})

    def close(self):
        return self.conn.close()

    def drop_table(self, table_name):
        """
        删除指定的表。

        Args:
            conn (sqlite3.Connection): 数据库连接对象
            table_name (str): 要删除的表名
        """
        drop_table_query = f"DROP TABLE IF EXISTS {table_name};"
        self.conn.execute(drop_table_query)
        self.conn.commit()
        logger.info("删除成功")

    def check_table_exists(self, table_name: str) -> bool:
        """检查某表是否存在

        Args:
            table_name (_str_): 表名

        Returns:
            _type_: _description_
        """
        try:
            self.cursor.execute(
                f"SELECT name FROM sqlite_master WHERE type='table' AND name='{table_name}'"
            )
            result = self.cursor.fetchall()
            # self.conn.close()
            return len(result) > 0
        except Exception as e:
            logger.error(f'检查表格是否存在时出错：{e}')
            return False

    def _exec(self, query: str):
        """执行SQL查询并返回结果
    
        Args:
            query (str): SQL查询语句
    
        Returns:
            list: 查询结果作为列表的列表，每一行为一个子列表
        """
        if query:
            try:
                self.cursor.execute(query)
                if query.strip().lower().startswith('select'):
                    result = self.cursor.fetchall()
                    return result
                else:
                    return True  # 非查询语句，操作成功
            except Exception as e:
                logger.error(f'执行SQL查询出错: {e}')
                return False

    def _init_table(self, dic: dict, PRIMARY: bool = False):
        """初始化表
        """
        _table_name = dic['表名']
        if self.check_table_exists(_table_name):
            haved = self._get_all_columns(_table_name)
            _new_cols = [
                f'{h} TEXT' for h in dic.values()
                if h not in haved and h != _table_name
            ]
            if len(_new_cols) == 0:
                logger.warning(f'无需新增字段')
            else:
                self._add_missing_columns(_table_name, _new_cols)
                logger.success(f'表新增字段成功')
        else:
            field = []
            for k, v in dic.items():
                if k != '表名':
                    if PRIMARY:
                        field.append(f'{v} TEXT PRIMARY KEY')
                        PRIMARY = False
                    else:
                        field.append(f'{v} TEXT')
            _query = ','.join(field)
            self._exec(query=f'''CREATE TABLE {_table_name} ({_query})''')
            self.conn.commit()
            logger.success(f'创建表{_table_name}成功')

    def create_notice_data_by_gruop(self, group_id):
        try:
            if self.check_table_exists(f'chat_data_by_{group_id}'):
                logger.info(f'表chat_data_by_{group_id}已存在')
                return True
            self._exec(f'''CREATE TABLE chat_data_by_{group_id} (
                    user_id TEXT,
                    message TEXT,
                    created_at TIMESTAMP
                    )
                         ''')
            logger.info(f'创建{group_id}_chat_data表成功')
            return True
        except Exception as e:
            logger.error(f'创建{group_id}_chat_data表出现错误: {e}')
            return False

    def add_column(self, table_name, column_name, data_type):
        """新增字段 
        Args:
            table_name (str): 表名
            column_name (str): 新字段名
            data_type (str): 字段数据类型
        """
        try:
            query = f'ALTER TABLE {table_name} ADD COLUMN {column_name} {data_type}'
            self.conn.execute(query)
            logger.info(f'新增字段{column_name} {data_type} 成功',
                        extra={"markup": True})
        except Exception as e:
            logger.error(f'新增字段出现错误: {e}')

    def remove_column(self, table_name, column_name):
        """删除字段
        Args:
            table_name (str): 表名
            column_name (str): 要删除的字段名
        """
        try:
            query = f'ALTER TABLE {table_name} DROP COLUMN {column_name}'
            self.conn.execute(query)
            logger.info(f'删除字段{column_name} 成功', extra={"markup": True})
            return True
        except Exception as e:
            logger.error(f'删除字段出现错误: {e}')
            return False

    def insert_row(self, table_name, values):
        """向表中插入一行数据
        Args:
            table_name (str): 表名
            values (dict): 包含字段名和对应值的字典
        """
        try:
            columns = ', '.join(values.keys())
            placeholders = ', '.join(['?'] * len(values))
            query = f'INSERT INTO {table_name} ({columns}) VALUES ({placeholders})'
            self.conn.execute(query, tuple(values.values()))
            self.conn.commit()
            logger.info(f'成功插入一行数据到表{table_name}', extra={"markup": True})
            return True
        except Exception as e:
            logger.error(f'插入数据时出现错误: {e}')
            return False

    def insert_or_update_row(self, table_name, values, unique_column):
        """
        如果数据不存在则插入一行数据，如果已存在则更新数据。

        Args:
            table_name (str): 表名
            values (dict): 包含字段名和对应值的字典
            unique_column (str): 用于检查唯一性的字段名
        """
        try:
            select_query = f'SELECT * FROM {table_name} WHERE {unique_column} = ?'
            existing_row = self.conn.execute(
                select_query, (values[unique_column], )).fetchone()

            if existing_row:
                update_values = {
                    column: value
                    for column, value in values.items()
                    if column != unique_column
                }
                self.update_row(table_name, update_values, unique_column,
                                values[unique_column])
                logger.info(f'成功更新表{table_name}中已存在的行数据',
                            extra={"markup": True})
            else:
                self.insert_row(table_name, values)
                logger.info(f'成功插入一行数据到表{table_name}', extra={"markup": True})
            return True
        except Exception as e:
            logger.error(f'插入或更新数据时出现错误: {e}')
            return False

    def insert_single_value(self, table_name, column_name, value):
        """向表中的单个字段插入值
        Args:
            table_name (str): 表名
            column_name (str): 字段名
            value: 要插入的值
        """
        try:
            query = f'INSERT INTO {table_name} ({column_name}) VALUES (?)'
            self.conn.execute(query, (value, ))
            self.conn.commit()
            logger.info(
                f'成功插入值 {value} ===> {str(column_name)} 于表 {table_name}',
                extra={"markup": True})
            return True
        except Exception as e:
            logger.error(f'插入值时出现错误: {e}')
            return False

    def _get_all_columns(self, table_name):
        """获取一个表的所有字段"""
        self.cursor.execute(f"PRAGMA table_info({table_name})")
        return [column[1] for column in self.cursor.fetchall()]

    def _add_missing_columns(self, table_name, new_columns):
        """添加缺少字段"""
        for new_column in new_columns:
            add_column_query = f"ALTER TABLE {table_name} ADD COLUMN {new_column} TEXT"
            logger.info(f'成功添加字段{new_column}于表 {table_name}',
                        extra={"markup": True})
            self.cursor.execute(add_column_query)

        self.conn.commit()
